package dao.mysql;

import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import model.BookingDTO;
import model.search.BookingSearchDTO;
import dao.BookingDAO;
import dao.DAOException;

public class BookingDAOImpl extends BaseDAO implements BookingDAO {
	public BookingDAOImpl() {
		super();
	}
	
	private ArrayList<BookingDTO> getList(PreparedStatement ps) throws SQLException {
		ArrayList<BookingDTO> list = new ArrayList<BookingDTO>();
		
		BookingDTO data = null;
		
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			data = new BookingDTO();
			data.setId(rs.getString("Id"));
			data.setPriority(rs.getInt("Priority"));
			data.setFacilityName(rs.getString("FacilityName"));
			data.setUserId(rs.getString("UserId"));
			data.setBookOn(rs.getDate("BookOn"));
			data.setStartDate(rs.getDate("StartDate"));
			data.setEndDate(rs.getDate("EndDate"));
			data.setContactDetail(rs.getString("ContactDetail"));
			data.setReason(rs.getString("Reason"));
			data.setEmergency(rs.getBoolean("Emergency"));
			data.setValidateBy(rs.getString("ValidateBy"));
			data.setValidateOn(rs.getDate("ValidateOn"));
			data.setStatus(rs.getString("Status"));
			list.add(data);
		}
		rs.close();
		
		return list;
	}
	
	public ArrayList<BookingDTO> getAll() throws DAOException {
		openConnection(false);

		ArrayList<BookingDTO> list = new ArrayList<BookingDTO>();
		try {
			String sql = "select * from Booking";
			PreparedStatement prep = this.getPreparedStatement(sql);
			list = getList(prep);
			prep.close();
		} catch (Exception e) {
			String error = "Error on Get All Booking. Nested Exception is: "
					+ e;
			super.printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
		return list;
	}
	
	public BookingDTO getBooking(String id) throws DAOException {
		openConnection(false);

		BookingDTO data = null;

		try {
			String sql = "select * from Booking where Id = ? ";
			PreparedStatement preq = getPreparedStatement(sql);
			preq.setString(1, id);
			ResultSet rs = preq.executeQuery();
			if (rs.next()) {
				data = new BookingDTO();
				data.setId(rs.getString("Id"));
				data.setPriority(rs.getInt("Priority"));
				data.setFacilityName(rs.getString("FacilityName"));
				data.setUserId(rs.getString("UserId"));
				data.setBookOn(rs.getDate("BookOn"));
				data.setStartDate(rs.getDate("StartDate"));
				data.setEndDate(rs.getDate("EndDate"));
				data.setContactDetail(rs.getString("ContactDetail"));
				data.setReason(rs.getString("Reason"));
				data.setEmergency(rs.getBoolean("Emergency"));
				data.setValidateBy(rs.getString("ValidateBy"));
				data.setValidateOn(rs.getDate("ValidateOn"));
				data.setStatus(rs.getString("Status"));
			}

			rs.close();
			preq.close();
		} catch (Exception e) {
			String error = "Error on GetBooking. Nested Exception is: " + e;
			super.printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}

		return data;
	}
	
	private String getWhereFilter(BookingSearchDTO src) {
		String where = "";
		
		if ( src.getFacilityName() != null && !src.getFacilityName().trim().isEmpty() ) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " ( FacilityName = ? ) ";
		}
		
		if ( src.getUserId() != null && !src.getUserId().trim().isEmpty() ) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " ( UserId = ? ) ";
		}
		
		boolean isDateFilter = false;
		if ( src.getStartDateFrom() != null && src.getStartDateTo() != null ) {
			if (where.isEmpty()) where = " WHERE ( "; else where += " AND ( ";
			if ( src.getStartDateFrom() == null ) {
				where += " ( Date(StartDate) <= ? ) ";
			} else if ( src.getStartDateTo() == null ) {
				where += " ( Date(StartDate) >= ? ) ";
			} else {
				where += " ( Date(StartDate) >= ? AND Date(StartDate) <= ? ) ";
			}
			isDateFilter = true;
		} 
		
		if ( src.getEndDateFrom() != null && src.getEndDateTo() != null ) {
			if (where.isEmpty()) where = " WHERE "; else if (isDateFilter) where += " OR "; else where += " AND ";
			if ( src.getEndDateFrom() == null ) {
				where += " ( Date(EndDate) <= ? ) ";
			} else if ( src.getStartDateTo() == null ) {
				where += " ( Date(EndDate) >= ? ) ";
			} else {
				where += " ( Date(EndDate) >= ? AND Date(EndDate) <= ? ) ";
			}
		}
		
		if ( isDateFilter ) where += " ) ";
		
		if ( src.getValidateBy() != null && !src.getValidateBy().trim().isEmpty() ) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " ( ValidateBy = ? ) ";
		}
		
		if ( src.getEmergency() != null ) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " ( Emergency = ? ) ";
		}
		
		if ( src.getStatus() != null && src.getStatus().size() > 0 ) {
			if (where.isEmpty()) where = " WHERE ( "; else where += " AND ( ";
			
			int i = 0;
			for ( String s : src.getStatus() ) {
				if ( i > 0 ) where += " OR ";
				where += " ( Status = ? ) ";
				i++;
			}
			
			where += " ) ";
		}
		
		if ( src.getReason() != null && !src.getReason().trim().isEmpty() ) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " ( Reason LIKE ? ) ";
		}
		
		if ( src.getManagerId() != null && !src.getManagerId().trim().isEmpty() ) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " ( UserId IN ( SELECT Id FROM User WHERE ManagerId = ? ) ) ";
		}
		System.out.println("WHERE: " + where);
		return where;
	}
	
	private ArrayList getParameters(BookingSearchDTO src) {
		ArrayList params = new ArrayList();
		
		if ( src.getFacilityName() != null && !src.getFacilityName().trim().isEmpty() ) {
			params.add(src.getFacilityName());
		}
		
		if ( src.getUserId() != null && !src.getUserId().trim().isEmpty() ) {
			params.add(src.getUserId());
		}
		
		if ( src.getStartDateFrom() != null && src.getStartDateTo() != null ) {
			if ( src.getStartDateFrom() == null ) {
				params.add(src.getStartDateTo());
			} else if ( src.getStartDateTo() == null ) {
				params.add(src.getStartDateFrom());
			} else {
				params.add(src.getStartDateFrom());
				params.add(src.getStartDateTo());
			}
		} 
		
		if ( src.getEndDateFrom() != null && src.getEndDateTo() != null ) {
			if ( src.getEndDateFrom() == null ) {
				params.add(src.getEndDateTo());
			} else if ( src.getStartDateTo() == null ) {
				params.add(src.getEndDateFrom());
			} else {
				params.add(src.getEndDateFrom());
				params.add(src.getEndDateTo());
			}
		}
		
		if ( src.getValidateBy() != null && !src.getValidateBy().trim().isEmpty() ) {
			params.add(src.getValidateBy());
		}
		
		if ( src.getEmergency() != null ) {
			params.add(src.getEmergency());
		}
		
		if ( src.getStatus() != null && src.getStatus().size() > 0 ) {
			for ( String s : src.getStatus() ) {
				params.add(s);
			}
		}
		
		if ( src.getReason() != null && !src.getReason().trim().isEmpty() ) {
			params.add("%" + src.getReason() + "%");
		}
		
		if ( src.getManagerId() != null && !src.getManagerId().trim().isEmpty() ) {
			params.add(src.getManagerId());
		}
		
		return params;
	}
	
	public ArrayList<BookingDTO> findBookingByCriteria(BookingSearchDTO bs)
			throws DAOException {
		openConnection();

		ArrayList<BookingDTO> list = new ArrayList<BookingDTO>();
		try {
			String sql = "";
			String where = "";
			ArrayList params = new ArrayList();

			sql = "SELECT * FROM Booking ";
			where = this.getWhereFilter(bs);
			params = this.getParameters(bs);

			PreparedStatement prep = getPreparedStatement(sql + " " + where);
			for (int i = 1; i <= params.size(); i++) {
				prep.setObject(i, params.get(i - 1));
				System.out.println(params.get(i-1));
			}

			printLog("Executing FindBookingByCriteria: " + sql + where + "[" + bs + "]", Level.INFO);
			
			list = getList(prep);
			
			prep.close();
		} catch (Exception e) {
			String error = "Error FindBookingByCriteria. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}

		return list;
	}
	
	@Override
	public void insertBooking(BookingDTO data) throws DAOException {
		openConnection(false);

		try {
			String sql = "INSERT INTO Booking " + 
		                 " ( Id, Priority, FacilityName, UserId, BookOn " +
					     " , StartDate, EndDate, ContactDetail, Reason, Emergency " +
						 " , ValidateBy, ValidateOn, Status " +
					     " ) VALUES " +
						 " ( ?, ?, ?, ?, NOW() " +
					     " , ?, ?, ?, ?, ? " +
						 " , ?, ?, ? )";
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, data.getId() ) ;
			prep.setInt(2, data.getPriority() ) ;
			prep.setString(3, data.getFacilityName());
			prep.setString(4, data.getUserId());
			//prep.setDate(5, new java.sql.Date( data.getBookOn().getTime() ));
			prep.setDate(5, new java.sql.Date( data.getStartDate().getTime() ));
			prep.setDate(6, new java.sql.Date( data.getEndDate().getTime() ));
			prep.setString(7, data.getContactDetail());
			prep.setString(8, data.getReason());
			prep.setBoolean(9, data.getEmergency());
			prep.setString(10, data.getValidateBy());
			prep.setDate(11, data.getValidateOn() == null ? null : new java.sql.Date( data.getValidateOn().getTime() ));
			prep.setString(12, data.getStatus());
			
			prep.executeUpdate();

			printLog("Executing Insert: " + sql, Level.INFO);

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Inserting Booking. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public void updateBooking(BookingDTO data) throws DAOException {
		openConnection(false);

		try {
			String sql = "UPDATE Booking " +
		                 "   SET Priority = ?, FacilityName = ? " +
					     "     , StartDate = ?, EndDate = ? " +
		                 "     , ContactDetail = ?, Reason = ? " +
					     "     , Emergency = ?, ValidateBy = ? " +
		                 "     , ValidateOn = ?, Status = ? " +
					     " WHERE Id = ? ";			
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setInt(1, data.getPriority());
			prep.setString(2, data.getFacilityName());
			prep.setDate(3, new java.sql.Date( data.getStartDate().getTime() ) ) ;
			prep.setDate(4, new java.sql.Date( data.getEndDate().getTime() ) ) ;
			prep.setString(5, data.getContactDetail());
			prep.setString(6, data.getReason());
			prep.setBoolean(7, data.getEmergency());
			prep.setString(8, data.getValidateBy() == null ? null : data.getValidateBy());
			prep.setDate(9, data.getValidateOn() == null ? null : new java.sql.Date( data.getValidateOn().getTime() ));
			prep.setString(10, data.getStatus());
			prep.setString(11, data.getId());
			prep.executeUpdate();

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Updating Booking. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}
	
	public String generateNumber() throws DAOException {
		Calendar c = Calendar.getInstance();
		int defaultNumber = c.get(Calendar.YEAR) * 1000000;
		int currentNumber = defaultNumber;
		
		System.out.println("Def: " + defaultNumber + " | Curr: " + currentNumber);
		
		openConnection(true);
		try {
			String sql = "Select MAX(Id) AS MaxID FROM Booking WHERE Id LIKE ? ";
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, String.valueOf(c.get(Calendar.YEAR)) + "%");
			
			ResultSet rs = prep.executeQuery();
			if ( rs.next() ) {
				if ( rs.getString("MaxID") != null && !rs.getString("MaxID").isEmpty() ) {
					currentNumber = Integer.parseInt(rs.getString("MaxID"));
				}
			}
			
			currentNumber++;
		} catch (SQLException e) {
			String error = "SQL Error on Getting Last Booking Number. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} catch (Exception e) {
			String error = "Error on Getting Last Booking Number. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
		return String.valueOf(currentNumber);
	}
	
	
}
